package com.weaver.drools.business.execution.service.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.weaver.drools.business.execution.entity.OverviewBean;
import com.weaver.drools.business.execution.service.IEAqPnlOverview146Service;
import com.weaver.drools.common.dao.EAqPnlOverview146Dao;
import com.weaver.drools.common.entity.EAqPnlOverview146;

import oracle.jdbc.OracleTypes;

@Service
public class EAqPnlOverview146ServiceImpl implements IEAqPnlOverview146Service {

	@Autowired
	private EAqPnlOverview146Dao overview146Dao;

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List findList(Long operateType, String porType, String currpageNum, String pageSize,
			String insUnit, String porStartDate1, String porStartDate2, String reportStarDate1, String reportStarDate2,
			String phaseId, String phaseActionId, String isArchived, String porId, String overViewId,
			Long isQueryDepartmentOtherReportUserRecord, Long departmentId, Long userId) throws Exception {

		List list=new ArrayList();
		List<OverviewBean> overViewList=new ArrayList<OverviewBean>();
		String sql="";
		if(operateType!=null && operateType==6L){//查询本单位下的行政执法数据
			sql="SELECT DISTINCT(A.PORID),A.BPTYPEID,A.PORTYPE FROM(SELECT A.PORID,A.PORTYPE,A.PARTIES,A.PORDATE,A.REPORTDATE,A.BPTYPEID,A.ISARCHIVED,A.USERID FROM E_AQ_PNL_POR_163 A,U_USR_USER_36 U36,U_USR_USER_36 U36_2 WHERE A.USERID=U36.USERID AND A.ISDELETED=0 AND U36.ISDELETED=0  AND U36_2.ISDELETED=0 AND U36.OPERATEPOINTID=U36_2.OPERATEPOINTID AND A.PORDATE IS NOT NULL AND to_char(A.PORDATE,'yyyy')=to_char(sysdate,'yyyy') AND U36_2.USERID="+userId+") A WHERE 1=1";
		}else if(operateType!=null && operateType==7L){//查询全区下的行政执法数据
			sql="SELECT DISTINCT(A.PORID),A.BPTYPEID,A.PORTYPE FROM(SELECT A.PORID,A.PORTYPE,A.PARTIES,A.PORDATE,A.REPORTDATE,A.BPTYPEID,A.ISARCHIVED,A.USERID FROM E_AQ_PNL_POR_163 A WHERE A.ISDELETED=0 AND A.PORDATE IS NOT NULL AND to_char(A.PORDATE,'yyyy')=to_char(sysdate,'yyyy')) A WHERE 1=1";
		}else{
			if(isQueryDepartmentOtherReportUserRecord!=null && isQueryDepartmentOtherReportUserRecord.toString().equals("1")){
				sql="SELECT DISTINCT(A.PORID),A.BPTYPEID,A.PORTYPE FROM(SELECT A.PORID,A.PORTYPE,A.PARTIES,A.PORDATE,A.REPORTDATE,A.BPTYPEID,A.ISARCHIVED,A.USERID FROM E_AQ_PNL_POR_163 A,Q_CMN_DEPARTMENT_23 Q23 WHERE A.USERID=Q23.USERID AND A.ISDELETED=0 AND Q23.ISDELETED=0 AND Q23.DEPARTMENTID="+departmentId+") A WHERE 1=1";
			}else{
				sql="SELECT DISTINCT(A.PORID),A.BPTYPEID,A.PORTYPE FROM(SELECT A.PORID,A.PORTYPE,A.PARTIES,A.PORDATE,A.REPORTDATE,A.BPTYPEID,A.ISARCHIVED,A.USERID FROM E_AQ_PNL_POR_163 A WHERE A.ISDELETED=0) A LEFT JOIN (SELECT Q733.USERID,E272.PORID FROM Q_SELF_EMPLOYEEUSER_733 Q733,Q_SELF_EMPLOYEE_69 Q69,Q_SELF_ENEMPLOYCONTACT_72 Q72,E_AQ_INS_UNDERTAKER_272 E272 WHERE Q733.EMPLOYEEID=Q69.EMPLOYEEID AND Q69.EMPLOYEEID=Q72.EMPLOYEEID AND Q72.CONTACTID=E272.CONTACTID AND Q733.ISDELETED=0 AND Q69.ISDELETED=0 AND Q72.ISDELETED=0 AND E272.ISDELETED=0) B ON A.PORID=B.PORID WHERE 1=1";
			}
		}
		String specialSql="";
		if(overViewId!=null && !overViewId.equals("")){
			specialSql+=" AND B.PNLOVERVIEWID="+overViewId;
		}
		if(porId!=null && !porId.equals("")){
			sql+=" AND A.PORID="+porId;
		}
		if(porType!=null && !porType.equals("")){
			sql+=" AND A.PORTYPE="+porType;
		}
		if(isArchived!=null && !isArchived.equals("")){
			sql+=" AND A.ISARCHIVED="+isArchived;
		}
		if(operateType!=null && operateType!=6L & operateType!=7L){
			if(isQueryDepartmentOtherReportUserRecord!=null && isQueryDepartmentOtherReportUserRecord.toString().equals("1")){
				sql+=" AND A.USERID!="+userId;
			}else{
				if(userId!=null && !userId.equals("")){
					sql+=" AND (A.USERID="+userId+" OR B.USERID="+userId+")";
				}
			}
		}
		if(porStartDate1!=null && !porStartDate1.equals("")){
			sql+=" AND A.PORDATE>=TO_DATE('"+porStartDate1+"', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(porStartDate2!=null && !porStartDate2.equals("")){
			sql+=" AND A.PORDATE<TO_DATE('"+porStartDate2+"', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(reportStarDate1!=null && !reportStarDate1.equals("")){
			sql+=" AND A.REPORTDATE>=TO_DATE('"+reportStarDate1+"', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(reportStarDate2!=null && !reportStarDate2.equals("")){
			sql+=" AND A.REPORTDATE<TO_DATE('"+reportStarDate2+"', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(insUnit!=null && !insUnit.equals("")){
			sql+=" AND A.PARTIES LIKE '%"+insUnit+"%'";
		}
		sql+=" ORDER BY A.PORID DESC";
		//开始连接存储过程
		Connection connection=overview146Dao.getConnection();
		CallableStatement cs=connection.prepareCall("{Call SP_SUM_OVERVIEW_PANDECT(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
		cs.setLong(1,operateType);
		cs.setString(2,"");
		cs.setString(3,"");
		cs.setString(4,currpageNum);
		cs.setString(5,pageSize);
		cs.setString(6,sql);
		cs.setString(7,specialSql);
		cs.setString(8,phaseId);
		cs.setString(9,phaseActionId);
		cs.setLong(10,userId);
		cs.setString(11,"");
		cs.setString(12,porType);
		cs.setString(13,insUnit);
		cs.setString(14,porStartDate1);
		cs.setString(15,porStartDate2);
		cs.registerOutParameter(16,OracleTypes.NUMBER);
		cs.registerOutParameter(17,OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs=(ResultSet)cs.getObject(17);
		while(rs.next()){
			OverviewBean overviewBean=new OverviewBean();
			//立案审批表编号
			overviewBean.setPorId(rs.getLong(1));
			overviewBean.setPorType(rs.getLong(2));
			//当前阶段名称
			overviewBean.setPhaseName(rs.getString(3));
			//立案时间
			overviewBean.setPorDate(rs.getDate(4));
			//立案审批表文书
			overviewBean.setWritName(rs.getString(5));
			//当事人
			overviewBean.setOperatePointName(rs.getString(6));
			//承办人
			overviewBean.setUndertaker(rs.getString(7));
			//结案剩余天数
			overviewBean.setFinalDates(rs.getLong(8));
			//当前流程类型ID
			overviewBean.setBpTypeId(rs.getLong(9));
			//当前流程对象ID
			overviewBean.setObjectId(rs.getLong(10));
			//下一步环节名称
			overviewBean.setNextBpTypeName(rs.getString(11));
			//下一步环节按钮的值
			overviewBean.setNextButtonValue(rs.getString(12));
			//是否已打印
			overviewBean.setIsPrint(rs.getLong(13));
			//是否禁用查看按钮
			overviewBean.setIsEnableViewButton(rs.getLong(14));
			//是否禁用修改按钮
			overviewBean.setIsEnableUpdateButton(rs.getLong(15));
			//是否禁用删除按钮
			overviewBean.setIsEnableDeletedButton(rs.getLong(16));
			//是否禁用打印按钮
			overviewBean.setIsEnablePrintButton(rs.getLong(17));
			//是否禁用延期按钮
			overviewBean.setIsEnableDelayButton(rs.getLong(18));
			//是否禁用调查取证按钮
			overviewBean.setIsEnableInvestigateButton(rs.getLong(19));
			//是否禁用下一环节按钮
			overviewBean.setIsEnableNextButton(rs.getLong(20));
			//下一步所需条件1
			overviewBean.setNextConditionId1(rs.getString(21));
			//下一步所需条件2
			overviewBean.setNextConditionId2(rs.getString(22));
			//下一步所需条件3
			overviewBean.setNextConditionId3(rs.getString(23));
			//下一步所需条件4
			overviewBean.setNextConditionId4(rs.getString(24));
			//下一步所需条件5
			overviewBean.setNextConditionId5(rs.getString(25));
			overViewList.add(overviewBean);
		}
		list.add(0,cs.getLong(16));
		list.add(1,overViewList);
		rs.close();
		cs.close();
		connection.close();
		return list;
	
	}

	@Override
	public EAqPnlOverview146 findById(Long pnloverviewid) {
		EAqPnlOverview146 overview146= overview146Dao.findEAqPnlOverview146ByPnloverviewid(pnloverviewid);
		return overview146;
	}

	@Override
	public void saveEAqPnlOverview146(EAqPnlOverview146 overview146) {
		overview146Dao.saveOrUpdate(overview146);
	}

	@Override
	public void updateEAqPnlOverview146(EAqPnlOverview146 overview146) {
		overview146Dao.saveOrUpdate(overview146);
	}

	@Override
	public void deleteById(Long pnloverviewid) {
		overview146Dao.deleteById(pnloverviewid);
		
	}
}
